package com.sky.report.service.impl;

import com.sky.constant.MessageConstant;
import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.exception.ExportExcelException;
import com.sky.exception.LocalDateException;
import com.sky.orders.service.OrderService;
import com.sky.report.service.ReportService;
import com.sky.user.service.UserService;
import com.sky.vo.*;
import com.sky.workspace.service.WorkSpaceService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

/**
 * 商家-数据统计 serviceImpl
 */
@Slf4j
@Service
public class ReportServiceImpl implements ReportService {


    @Resource
    private OrderService orderService;

    @Resource
    private UserService userService;

    @Resource
    private WorkSpaceService workSpaceService;




    /**
     * 数据统计
     *        时间范围，营业额统计
     * @param begin
     * @param end
     */
    @Override
    public TurnoverReportVO getTurnOverStatistics(LocalDate begin, LocalDate end) {
        // 1.检验 开始时间 是否小于 结束时间
        if(begin.isAfter(end)){
            //表示开始时间大于结束时间，参数异常，抛出异常
            throw new LocalDateException(MessageConstant.LOCAL_DATE_ERROR);
        }

        // 2.创建List集合，收集开始时间以及结束时间和中间间隔的所有日期
        List<LocalDate> localDateList = new ArrayList<>();
        // 将第一天添加到集合
        localDateList.add(begin);

        // 3.获取 开始到结束，所有天数
        while ( !begin.equals(end) && begin.isBefore(end)) {
            //当前日期 +1天
            begin = begin.plusDays(1);
            localDateList.add(begin);
        }

        // 4.将所有天数拼接为字符串
        String dateListStr = localDateList.stream().map(String::valueOf).collect(Collectors.joining(","));

        // 5.创建集合 收集每天营业总额
        List<BigDecimal> dayTotalAmountList = new ArrayList<>();
        // 6.遍历集合 获取所有日期  --> yyyy-MM-dd
        for (LocalDate localDate : localDateList) {
            LocalDateTime startTime = LocalDateTime.of(localDate, LocalTime.MIN);  // yyyy-MM-dd 00:00:00
            LocalDateTime endTime = LocalDateTime.of(localDate, LocalTime.MAX);    // yyyy-MM-dd 23:59:59

            // SQL: select sum(amount) from orders where order_time >= #{startTime} and order_time <= #{endTime} and status = 5
            //封装参数
            Map map = new ConcurrentHashMap();
            map.put("startTime", startTime);
            map.put("endTime", endTime);
            map.put("status", Orders.COMPLETED);
            //查询
            BigDecimal dayTotalAmount = orderService.selectDayTotalAmountByMap(map);
            //检验
            dayTotalAmount = dayTotalAmount == null ? new BigDecimal("0.00") : dayTotalAmount;
            //收集到集合
            dayTotalAmountList.add(dayTotalAmount);
        }
        // 7.将每天的营业总额拼接位字符串
        String turnoverListStr = dayTotalAmountList.stream().map(BigDecimal::toPlainString).collect(Collectors.joining(","));

        // 8.封装返回值
        return TurnoverReportVO.builder().dateList(dateListStr).turnoverList(turnoverListStr).build();
    }



    /**
     * 数据统计
     *        时间范围，用户量统计
     * @param begin
     * @param end
     */
    @Override
    public UserReportVO userCountStatistics(LocalDate begin, LocalDate end) {
        // 1.检验 开始时间 是否小于 结束时间
        if(begin.isAfter(end)){
            //表示开始时间大于结束时间，参数异常，抛出异常
            throw new LocalDateException(MessageConstant.LOCAL_DATE_ERROR);
        }
        // 2.创建集合，收集开始时间和结束时间以及中间间隔的所有日期
        List<LocalDate> dateList = new ArrayList<>();
        // 将第一天添加到集合
        dateList.add(begin);

        // 3.获取 开始到结束，所有天数
        while ( !begin.equals(end) && begin.isBefore(end)) {
            // 当前日期 +1天
            begin = begin.plusDays(1);
            dateList.add(begin);
        }

        // 4.将所有天数拼接为字符串
        String dateListStr = dateList.stream().map(String::valueOf).collect(Collectors.joining(","));

        // 集合，收集每天新增用户
        List<Integer> dayAddUserCountList = new ArrayList<>();
        // 集合，收集当天结束前的系统总用户量
        List<Integer> userCountList = new ArrayList<>();

        // 6.遍历集合, 取出所有日期 --> yyyy-MM-dd
        for (LocalDate localDate : dateList) {
            LocalDateTime startTime = LocalDateTime.of(localDate, LocalTime.MIN); // yyyy-MM-dd 00:00:00
            LocalDateTime endTime = LocalDateTime.of(localDate, LocalTime.MAX);   // yyyy-MM-dd 23:59:59

            //封装参数
            // SQL: select count(id) from user where create_time <= #{endTime}
            Map map = new ConcurrentHashMap();
            map.put("endTime", endTime);
            // 查询当天结束前 系统用户总数量
            Integer countUser = userService.selectUserCountByMap(map);
            // 校验
            countUser = countUser == null ? 0 : countUser;
            // 收集当天结束前 系统总用户量
            userCountList.add(countUser);

            //封装参数
            // SQL: select count(id) from user where create_time >= #{startTime} and create_time <= #{endTime}
            map.put("startTime", startTime);
            // 查询当天新增用户数量
            Integer dayUserCount = userService.selectUserCountByMap(map);
            // 检验
            dayUserCount = dayUserCount == null ? 0 : dayUserCount;
            // 收集当天新增用户
            dayAddUserCountList.add(dayUserCount);
        }

        // 7.将每天的用户新增拼接成字符串
        String newUserList = dayAddUserCountList.stream().map(String::valueOf).collect(Collectors.joining(","));
        // 8.将每天结束前，系统用户总量拼接成字符串
        String totalUserList = userCountList.stream().map(String::valueOf).collect(Collectors.joining(","));

        // 9.返回
        return UserReportVO.builder().dateList(dateListStr).newUserList(newUserList).totalUserList(totalUserList).build();
    }



    /**
     * 数据统计
     *        订单相关统计
     * @param begin
     * @param end
     */
    @Override
    public OrderReportVO ordersCountStatistics(LocalDate begin, LocalDate end) {
        // 1.检验 开始时间 是否小于 结束时间
        if(begin.isAfter(end)){
            //表示开始时间大于结束时间，参数异常，抛出异常
            throw new LocalDateException(MessageConstant.LOCAL_DATE_ERROR);
        }
        // 2.创建集合，收集开始时间和结束时间以及中间间隔的所有日期
        List<LocalDate> localDateList = new ArrayList<>();
        // 将第一天添加到集合
        localDateList.add(begin);

        // 3.获取 开始到结束，所有天数
        while ( !begin.equals(end) && begin.isBefore(end)) {
            // 当前日期 +1天
            begin = begin.plusDays(1);
            localDateList.add(begin);
        }


        // 集合，收集每日订单数
        List<Integer> dayOrderCountList = new ArrayList<>();
        // 集合，收集每日已完成的订单数
        List<Integer> daySuccessOrderCountList = new ArrayList<>();

        // 遍历日期集合, 取出所有日期 --> yyyy-MM-dd
        for (LocalDate localDate : localDateList) {
            LocalDateTime dayStartTime = LocalDateTime.of(localDate, LocalTime.MIN); // yyyy-MM-dd 00:00:00
            LocalDateTime dayEndTime = LocalDateTime.of(localDate, LocalTime.MAX);   // yyyy-MM-dd 23:59:59

            // 封装参数
            Map dayMap = new ConcurrentHashMap();
            dayMap.put("startTime", dayStartTime);
            dayMap.put("endTime", dayEndTime);
            // 查询当天订单数
            // SQL: select count(id) from orders where order_time >= #{startTime} and order_time <= #{endTime}
            Integer dayOrderCount = orderService.getOrderStatisticsByMap(dayMap);
            // 校验
            dayOrderCount = dayOrderCount == null ? 0 : dayOrderCount;
            // 收集到集合
            dayOrderCountList.add(dayOrderCount);

            // 封参
            dayMap.put("status", Orders.COMPLETED);
            // 查询当天已完成订单数
            // SQL: select count(id) from orders where order_time >= #{startTime} and order_time <= #{endTime} and status = #{status}
            Integer dayOrderSuccessCount = orderService.getOrderStatisticsByMap(dayMap);
            // 校验
            dayOrderSuccessCount = dayOrderSuccessCount == null ? 0 : dayOrderSuccessCount;
            // 收集到集合
            daySuccessOrderCountList.add(dayOrderSuccessCount);
        }

        // 计算开始到结束时间的区间总订单数
        Integer totalOrderCount = dayOrderCountList.stream().reduce(Integer::sum).get();
        // 计算始到结束时间的区间已完成订单数
        Integer orderSuccessCount = daySuccessOrderCountList.stream().reduce(Integer::sum).get();

        //计算订单 完成率
        Double successRate = 0.00;
        if(totalOrderCount != 0) {
            BigDecimal orderCountBig = new BigDecimal(String.valueOf(totalOrderCount));
            BigDecimal orderSuccessCountBig = new BigDecimal(String.valueOf(orderSuccessCount));
            // 已完成订单总数 / 订单总数 = 完成率
            successRate = orderSuccessCountBig.divide(orderCountBig, 2, BigDecimal.ROUND_HALF_DOWN).doubleValue();
        }


        // 日期拼接字符串
        String dateList = localDateList.stream().map(String::valueOf).collect(Collectors.joining(","));
        // 每日订单数拼接字符串
        String orderCountList = dayOrderCountList.stream().map(String::valueOf).collect(Collectors.joining(","));
        // 每日已完成订单数拼接字符串
        String validOrderCountList = daySuccessOrderCountList.stream().map(String::valueOf).collect(Collectors.joining(","));

        // 封装返回
        return OrderReportVO.builder()
                .dateList(dateList)
                .totalOrderCount(totalOrderCount)
                .validOrderCount(orderSuccessCount)
                .orderCompletionRate(successRate)
                .orderCountList(orderCountList)
                .validOrderCountList(validOrderCountList)
                .build();
    }



    /**
     * 数据统计
     *        热销商品Top10
     * @param begin
     * @param end
     */
    @Override
    public SalesTop10ReportVO salesTop10Ststistics(LocalDate begin, LocalDate end) {
        // 1.检验 开始时间 是否小于 结束时间
        if (begin.isAfter(end)) {
            //表示开始时间大于结束时间，参数异常，抛出异常
            throw new LocalDateException(MessageConstant.LOCAL_DATE_ERROR);
        }

        // 2.日期 拼接 时、分、秒
        LocalDateTime startTime = LocalDateTime.of(begin, LocalTime.MIN); // yyyy-MM-dd 00:00:00
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);     // yyyy-MM-dd 23:59:59

        // 3.封装参数
        Map map = new HashMap();
        map.put("startTime", startTime);
        map.put("endTime", endTime);
        map.put("status", Orders.COMPLETED);
        List<GoodsSalesDTO> goodsSalesDTOList  = orderService.selectSalesTop10Statistics(map);

        //销量前十数据判空
        if(CollectionUtils.isEmpty(goodsSalesDTOList)){
            return SalesTop10ReportVO.builder().build();
        }

        // 4.创建收集对象
        StringBuffer names = new StringBuffer();
        StringBuffer numbers = new StringBuffer();

        // 5.遍历集合
        for (GoodsSalesDTO goodsSalesDTO : goodsSalesDTOList) {
            names.append(goodsSalesDTO.getName()).append(",");
            Integer number = goodsSalesDTO.getNumber() == null ? 0 : goodsSalesDTO.getNumber();
            numbers.append(number).append(",");
        }

        // 6.去掉末尾的 ","
        String nameList = names.toString();
        nameList = nameList.substring(0, nameList.lastIndexOf(","));
        String numberList = numbers.toString();
        numberList = numberList.substring(0, numberList.lastIndexOf(","));

        //返回
        return SalesTop10ReportVO.builder().nameList(nameList).numberList(numberList).build();
    }



    /**
     * 数据导出
     *        计算从前一天开始，前30天的数据
     * @param response
     */
    @Override
    public void exportBusinessData(HttpServletResponse response) {
        log.info("开始导出Excel文档--" + LocalDateTime.now());

        // 前一天
        LocalDate yesterdayLocalDate = LocalDate.now().minusDays(1);
        // 前30天 //不包括今天 往前推30天
        LocalDate startDate = LocalDate.now().minusDays(30);

        // 定义临时变量保存开始当天
        LocalDate tempDate = startDate;

        // 计算这30天日期  经过计算之后 startDate == yesterdayLocalDate
        List<LocalDate> localDateList = new ArrayList<>();
        localDateList.add(startDate);     // 添加第一天
        while( !startDate.equals(yesterdayLocalDate) && startDate.isBefore(yesterdayLocalDate) ){
            startDate = startDate.plusDays(1);
            localDateList.add(startDate);
        }


        // 1.计算30天总的数据概览                                        // yyyy-MM-dd 00:00:00                       // yyyy-MM-dd 23:59:59
        BusinessDataVO businessDataVO = workSpaceService.businessData(LocalDateTime.of(startDate, LocalTime.MIN), LocalDateTime.of(yesterdayLocalDate, LocalTime.MAX));

        // 2.读取本地工作工作簿模版
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        try {
            // 3.创建内存中的工作簿
            Workbook workbook = WorkbookFactory.create(inputStream);
            // 4.获取sheet页
            Sheet sheet1 = workbook.getSheetAt(0); //sheet页 索引从0开始计算

            //sheet页左上角第一个表格坐标(0,0) 表示: 行从0索引开始，列从0索引开始
            // 5. 《概览数据》赋值
            Row row = sheet1.getRow(1); // 索引0第一行，索引1表示第2行
            Cell cell = row.getCell(1); // 索引0第一列，索引1表示第2列
            cell.setCellValue("时间：" + tempDate + " 至 " + yesterdayLocalDate);

            row = sheet1.getRow(3);     //Excel第4行
            row.getCell(2).setCellValue(businessDataVO.getTurnover());           //营业额
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());//订单完成率
            row.getCell(6).setCellValue(businessDataVO.getNewUsers());           //新增用户数
            row = sheet1.getRow(4);     //Excel第五行
            row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());    //有效订单
            row.getCell(4).setCellValue(businessDataVO.getUnitPrice());          //平均订单价


            // 6.每天的明细数据
            for (int i = 0; i < localDateList.size(); i++) {
                // 每一天日期
                LocalDate localDate = localDateList.get(i);
                // 查询从开始时间 每天的明细数据
                BusinessDataVO businessData = workSpaceService.businessData(LocalDateTime.of(localDate, LocalTime.MIN), LocalDateTime.of(localDate, LocalTime.MAX));
                // 为空跳过
                if(Objects.isNull(businessData)){
                    continue;
                }

                //sheet页第8行。也就是索引7开始填充  依次往下+1行
                row = sheet1.getRow(7 + i);
                row.getCell(1).setCellValue(localDate.toString());
                row.getCell(2).setCellValue(businessData.getTurnover());
                row.getCell(3).setCellValue(businessData.getValidOrderCount());
                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessData.getUnitPrice());
                row.getCell(6).setCellValue(businessData.getNewUsers());
            }

            // 7.获取输出流对象
            ServletOutputStream outputStream = response.getOutputStream();

            // 8.工作簿写出
            workbook.write(outputStream);
            outputStream.flush();

            //9.释放资源
            workbook.close();
            outputStream.close();
            log.info("导出数据成功--" + LocalDateTime.now());
        } catch (Exception e) {
            throw new ExportExcelException(MessageConstant.EXPORT_EXCEL_ERROR);
        }
    }


}
